<?php
namespace sql;
use utils\ConfigReader;
use sql\SqlCmdReader;
use database\DbHelper;
use utils\SysTool;

class SqlExec
{
    var $m_table;
    var $m_valSet;
    var $m_cmdRd;
    
    function __construct()
    {
        $this->m_valSet = new SqlValSetter();
        $this->m_cmdRd = new SqlCmdReader();
    }
    
    function __destruct()
    {
    }
    
    /**
     * 执行SQL
     * @param string $table
     * @param string $sql
     * @param string $fields
     * @param string $where
     * @param array $paras 条件关联数组
     */
    function exec($table,$sql,$fields,$where,$paras)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($fields);
        $field_where = $this->selFields($where);
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $this->m_valSet->setVal($cmd, $field_sel, $paras);
        $this->m_valSet->setVal($cmd, $field_where, $paras);
        $db->ExecuteNonQuery($cmd);
    }
    
    function exe_sql($sql)
    {
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $db->ExecuteNonQuery($cmd);
    }
    
    /**
     * 执行SQL
     * @param string $table
     * @param string $sql
     * @param string $fields
     * @param string $newNames
     * @return array[]
     */
    function exec_arr($table,$sql,$fields,$newNames)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($fields);
        
        $names = explode(",", $fields);
        if( !empty($newNames) ) $names = explode(",", $newNames);
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $ret = $db->ExecuteDataSet($cmd);
        
        $arr = array();
        foreach($ret as $row)
        {
            $fieldVals = array();
            for($i=0,$l=count($field_sel);$i<$l;++$i)
            {
                $field = $field_sel[$i];
                $name = $names[$i];
                $fieldVals[$name] = $this->m_cmdRd->readField($row, $field);
            }
            $arr[] = $fieldVals;            
        }
        return $arr;
    }
    
    /**
     * 批量执行SQL
     * @param string $table
     * @param string $sql
     * @param string $fields
     * @param string $where
     * @param array $vals [array(),array()]
     */
    function exec_batch($table,$sql,$fields,$where,$vals)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($fields);
        $field_where = $this->selFields($where);
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        
        for($i = 0 , $l = count($vals) ; $i < $l;++$i)
        {
            $this->m_valSet->setVal($cmd, $field_sel, $vals[$i]);
            $this->m_valSet->setVal($cmd, $field_where, $vals[$i]);
            $db->ExecuteNonQuery($cmd);
        }
    }
        
    /**
     * 插入数据
     * @param string $table
     * @param array $arr 关联数组,id,1
     */
    function insert($table,$arr)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($arr);
        $sql_fields = $this->toSqlFields($arr);
        $sql_pars = $this->toSqlParams($arr);
        $sql = "insert into $table ($sql_fields) values($sql_pars);";
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $this->m_valSet->setVal($cmd, $field_sel, $arr);
        $db->ExecuteNonQuery($cmd);
    }
    
    /**
     * 更新
     * @param string $table
     * @param string $fields
     * @param string $where
     * @param array $val 关联数组
     */
    function update($table,$fields,$where,$val)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($fields);
        $field_cdt = $this->selFields($where);
        $set_fields = $this->toSqlParSet($fields);
        $set_cdt = $this->toSqlCondition($where);
        $sql = "update $table set $set_fields where $set_cdt";
       
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $this->m_valSet->setVal($cmd, $field_sel, $val);
        $this->m_valSet->setVal($cmd, $field_cdt, $val);
        $db->ExecuteNonQuery($cmd);
    }
    
    /**
     * 更新
     * @param string $table
     * @param array $arrField 字段关联数组
     * @param array $arrWhere 条件关联数组
     */
    function update2($table,$arrField,$arrWhere)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($arrField);
        $field_cdt = $this->selFields($arrWhere);
        $set_fields = $this->toSqlParSet($arrField);
        $set_cdt = $this->toSqlCondition($arrWhere);
        $sql = "update $table set $set_fields where $set_cdt";
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $this->m_valSet->setVal($cmd, $field_sel, $arrField);
        $this->m_valSet->setVal($cmd, $field_cdt, $arrWhere);
        $db->ExecuteNonQuery($cmd);
    }
    
    /**
     * 更新
     * @param string $table
     * @param array $arrField 字段关联数组
     * @param array $where
     */
    function update3($table,$arrField,$where)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($arrField);
        $set_fields = $this->toSqlParSet($arrField);
        $sql = "update $table set $set_fields where $where";
    
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $this->m_valSet->setVal($cmd, $field_sel, $arrField);
        $db->ExecuteNonQuery($cmd);
    }
    
    /**
     * 更新
     * @param string $table
     * @param string $fields     
     * @param array $val 条件关联数组,null
     */
    function select($table,$fields,$val)
    {
        //where是字符串
        if(is_string($val)) return $this->selectByString($table, $fields, $val);
        
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($fields);
        $field_cdt = $this->selFields($val);
        $sql_cdt = $this->toSqlCondition($val);
        $sql_fields = $this->toSqlFields($field_sel);
        
        $sql = "select $sql_fields from $table where $sql_cdt";
        if(empty($sql_cdt)) $sql = "select $sql_fields from $table";
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $this->m_valSet->setVal($cmd, $field_cdt, $val);
        $rs = $db->ExecuteDataSet($cmd);
        $arr = array();
        foreach($rs as $row)
        {
            $arr[] = $this->m_cmdRd->readFields($row, $field_sel);
        }
        return $arr;
    }
    
    function selectByString($table,$fields,$where)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($fields);
        $sql_fields = $this->toSqlFields($field_sel);
        $sql = "select $sql_fields from $table where $where";
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $rs = $db->ExecuteDataSet($cmd);
        $arr = array();
        foreach($rs as $row)
        {
            $arr[] = $this->m_cmdRd->readFields($row, $field_sel);
        }
        return $arr;
    }
    
    /**
     * 获取分页数据
     * @param string $table
     * @param string $fields
     * @param string $where
     * @param string $sort
     * @param string $pageSize
     * @param string $pageIndex
     * @return array[]
     */
    function page($table,$fields,$where,$sort,$pageSize,$pageIndex)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($fields);//字段
        $sql_fields = $this->toSqlFields($field_sel);
        
        $rowStart = $pageSize *($pageIndex-1);
        if(!empty($where)) $where = "where " . $where;
        if(!empty($sort)) $sort = "order by " . $sort;
        $sql = "select $sql_fields from $table $where $sort limit $rowStart,$pageSize";
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $rs = $db->ExecuteDataSet($cmd);
        $arr = array();
        foreach($rs as $row)
        {
            $arr[] = $this->m_cmdRd->readFields($row, $field_sel);
        }
        return $arr;
    }
    
    /**
     * 删除
     * @param string $table
     * @param array $val 关联数组
     */
    function delete($table,$val)
    {
        $this->m_table = $this->table($table);
        $field_cdt = $this->selFields($val);
        $set_cdt = $this->toSqlCondition($val);
        $sql = "delete from $table where $set_cdt";
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);        
        $this->m_valSet->setVal($cmd, $field_cdt, $val);
        $db->ExecuteNonQuery($cmd);
    }
    
    /**
     * 统计
     * @param string $table
     * @param array $val 关联数组
     */
    function count($table,$val)
    {
        if( is_string($val)) return $this->count_str($table, $val);
        
        $this->m_table = $this->table($table); 
        $field_cdt  = $this->selFields($val);
        $set_cdt    = $this->toSqlCondition($val);
        $sql = "select count(*) from $table where $set_cdt";
        if( empty($set_cdt)) $sql = "select count(*) from $table;";
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $this->m_valSet->setVal($cmd, $field_cdt, $val);
        return (int)$db->ExecuteScalar($cmd);
    }
    
    function count_str($table,$where)
    {
        $this->m_table = $this->table($table);
        $sql = "select count(*) from $table where $where";
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        return (int)$db->ExecuteScalar($cmd);
    }
    
    /**
     * 读取一条数据
     * @param string $table
     * @param string  $fields
     * @param array $val 条件值，关联数组 id,1
     */
    function read($table,$fields,$vals)
    {
        $this->m_table = $this->table($table);
        $field_sel = $this->selFields($fields);
        $field_cdt = $this->selFields($vals);
        $sql_where = $this->toSqlCondition($vals);

        $sql = "select $fields from $table where $sql_where";        
        
        $db = new DbHelper();
        $cmd = $db->prepare_utf8($sql);
        $this->m_valSet->setVal($cmd, $field_cdt, $vals);
        $row = $db->ExecuteRow($cmd);
        if( empty($row) ) return $row;
        return $this->m_cmdRd->readFields($row, $field_sel);
    }
    
    /**
     * 
     * @param string $fields a,b,c,d,e,f,g
     * @return array
     */
    function selFields($fields)
    {
        if( empty($fields) ) return null;
        
        $sys = new SysTool();
        if( $sys->isAssocArray($fields) ) return $this->selFieldsFromArr($fields);
        
        if( strlen($fields) < 1 ) return null;
        if( strcasecmp($fields,"*") == 0) return $this->m_table->get("$.fields")[0];
        
        $field_sel = array();
        $arr = explode(",", $fields);
        for($i = 0 , $l = count($arr) ; $i< $l ; ++$i)
        {
            $name = $arr[$i];
            $ret = $this->m_table->get("$.fields[?(@.name=='$name')]");
            $field_sel[] = $ret[0];
        }
        return $field_sel;
    }
    
    /**
     * 
     * @param array $arr 关联数组。
     * @return NULL
     */
    function selFieldsFromArr($arr)
    {
        if( empty($arr)) return null;
        
        $fields = array();
        foreach($arr as $ak => $av)
        {
            $fields[] = $ak;
        }
        
        return $this->selFields(join(",",$fields));
    }
    
    function table($tableName)
    {
        $cr = new ConfigReader();
        return $cr->module("database.$tableName");
    }
    
    /**
     * 将关联数组参数转换成条件语句。name=:name and id=:id
     * @param unknown $sp
     */
    function toSqlCondition($sp)
    {
        if( empty($sp)) return null;
        
        if( is_string($sp))
        {
            $fs = explode(",", $sp);
            $arr = array();
            for($i=0,$l=count($fs);$i<$l;++$i)
            {
                $n = $fs[$i];
                $arr[] = "$n=:$n";
            }
            return join(",",$arr);
        }        
        
        //关联数组
        $sys = new SysTool();
        $arr = array();
        
        if( $sys->isAssocArray( $sp) )
        {
            foreach($sp as $ak => $av)
            {
                $arr[] = "$ak=:$ak";
            }
            return join(" and ",$arr);
        }        
    }
    
    /**
     * 将关联数组转换成SQL字段列表。id,name,age,sex
     * @param array $sp 关联数组
     * @return string
     */
    function toSqlFields($sp)
    {
        if(empty($sp)) return null;
        
        //关联数组
        $sys = new SysTool();
        if( $sys->isAssocArray($sp) )
        {
            $arr = array();
            foreach($sp as $ak => $av)
            {
                $arr[] = "$ak";
            }
            return join(",",$arr);
        }//普通索引数组，JSON数组
        else {
            
            $arr = array();
            for($i=0,$l=count($sp);$i<$l;++$i)
            {
                $field = $sp[$i];
                $name = $field["name"];
                $arr[] = $name;
            }
            
            return join(",",$arr);
            
        }
    }
    
    /**
     * 将关联数组转换成SQL字段列表。:id,:name,:age,:sex
     * @param array $sp 关联数组
     * @return string
     */
    function toSqlParams($sp)
    {
        if(empty($sp)) return null;
        
        $arr = array();
        foreach($sp as $ak => $av)
        {
            $arr[] = ":$ak";
        }
        return join(",",$arr);
    }
    
    /**
     * 将关联数组转换成SQL赋值语句。id=:id,name=:name,age=:age,sex=:sex
     * @param array $sp 关联数组
     * @return string
     */
    function toSqlParSet($sp)
    {
        if(empty($sp)) return null;
        
        if( is_string($sp))
        {
            $fs = explode(",", $sp);
            $arr = array();
            for($i=0,$l=count($fs);$i<$l;++$i)
            {
                $n = $fs[$i];
                $arr[] = "$n=:$n";
            }
            return join(",",$arr);
        }
        
        $arr = array();
        foreach($sp as $ak => $av)
        {
            $arr[] = "$ak=:$ak";
        }
        return join(",",$arr);
    }
}
?>